﻿using System;
using System.Collections.Generic;
using System.Text;
using NIIT.SaoBacDau.Aupi.HospitalManagement.Entities;
using System.Data.SqlClient;
using System.Data;

namespace NIIT.SaoBacDau.Aupi.HospitalManagement.DataAccess
{
    public class DAO_E_DrugDetails
    {
        public static bool AddDrugDetails(E_DrugDetails drug)
        {
            SqlConnection conn = DAO_ConnectDatabase.Connect();

            try
            {
                conn.Open();
                SqlCommand sqlcom = new SqlCommand("insert into Equipment.DrugDetails (DrugID,DrugName,Unit,Price,Quantity,Source,ProductionDate,UseTime,UseGuide,AddedWareHouseDate,DrugTypeID) values(@DrugID,@DrugName,@Unit,@Price,@Quantity,@Source,@ProductDate,@UseTime,@UseGuide,@Added,@DrugTypeID)", conn);
                sqlcom.Parameters.Add("@DrugID", SqlDbType.Int).Value = drug.DrugID;
                sqlcom.Parameters.Add("@DrugTypeID", SqlDbType.Int).Value = drug.DrugTypeID;
                sqlcom.Parameters.Add("@DrugName", SqlDbType.NVarChar).Value = drug.NameDrug;
                sqlcom.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = drug.Unit;
                sqlcom.Parameters.Add("@Price", SqlDbType.Decimal).Value = Convert.ToDecimal(drug.Price);
                sqlcom.Parameters.Add("@Quantity", SqlDbType.Int).Value = drug.Quantity;
                sqlcom.Parameters.Add("@Source", SqlDbType.NVarChar).Value = drug.Source;
                sqlcom.Parameters.Add("@ProductDate", SqlDbType.DateTime).Value = drug.Productdate;
                sqlcom.Parameters.Add("@UseTime", SqlDbType.DateTime).Value = drug.UseTime;
                sqlcom.Parameters.Add("@UseGuide", SqlDbType.NVarChar).Value = drug.UseGuide;
                sqlcom.Parameters.Add("@Added", SqlDbType.DateTime).Value = drug.AddedWareHouseDate;
                int Excute = sqlcom.ExecuteNonQuery();
                if (Excute == 1)
                    return true;
                else
                    return false;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        public static bool EditDrugDetails(E_DrugDetails drug)
        {
            SqlConnection conn = DAO_ConnectDatabase.Connect();
            try
            {
                conn.Open();
                SqlCommand sqlcom = new SqlCommand("UPDATE Equipment.DrugDetails SET [DrugID] = @DrugID,[DrugName]=@DrugName,[Unit]=@Unit,[Price]=@Price,[Quantity]=@Quantity,[Source]=@Source,[ProductionDate] = @ProductionDate,[UseTime]=@UseTime,[UseGuide]=@UseGuide,[AddedWareHouseDate]=@AddedWareHouseDate WHERE DrugID = @DrugID", conn);
                sqlcom.Parameters.Add("@DrugID", SqlDbType.Int).Value = drug.DrugID;
                //sqlcom.Parameters.Add("@DrugTypeID", SqlDbType.Int).Value = drug.DrugTypeID;
                sqlcom.Parameters.Add("@DrugName", SqlDbType.NVarChar).Value = drug.NameDrug;
                sqlcom.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = drug.Unit;
                sqlcom.Parameters.Add("@Price", SqlDbType.Decimal).Value = drug.Price;
                sqlcom.Parameters.Add("@Quantity", SqlDbType.Int).Value = drug.Quantity;
                sqlcom.Parameters.Add("@Source", SqlDbType.NVarChar).Value = drug.Source;
                sqlcom.Parameters.Add("@ProductionDate", SqlDbType.DateTime).Value = drug.Productdate;
                sqlcom.Parameters.Add("@UseTime", SqlDbType.DateTime).Value = drug.UseTime;
                sqlcom.Parameters.Add("@UseGuide", SqlDbType.NVarChar).Value = drug.UseGuide;
                int Excute = sqlcom.ExecuteNonQuery();
                if (Excute == 1)
                    return true;
                else
                    return false;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        public static void DeleteDrugDetails(E_DrugDetails drug)
        {
            SqlConnection conn = DAO_ConnectDatabase.Connect();
            try
            {
                conn.Open();
                SqlCommand sqlcom = new SqlCommand("DELETE FROM  Equipment.DrugDetails  WHERE DrugID = @DrugID", conn);

                sqlcom.Parameters.Add("@DrugID", SqlDbType.Int).Value = drug.DrugID;
                sqlcom.Parameters.Add("@DrugTypeID", SqlDbType.Int).Value = drug.DrugTypeID;
                sqlcom.Parameters.Add("@DrugName", SqlDbType.NVarChar).Value = drug.NameDrug;
                sqlcom.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = drug.Unit;
                sqlcom.Parameters.Add("@Price", SqlDbType.Decimal).Value = drug.Price;
                sqlcom.Parameters.Add("@Quantity", SqlDbType.Int).Value = drug.Quantity;
                sqlcom.Parameters.Add("@Source", SqlDbType.NVarChar).Value = drug.Source;
                sqlcom.Parameters.Add("@ProductDate", SqlDbType.DateTime).Value = drug.Productdate;
                sqlcom.Parameters.Add("@UseTime", SqlDbType.DateTime).Value = drug.UseTime;
                sqlcom.Parameters.Add("@UseGuide", SqlDbType.NVarChar).Value = drug.UseGuide;
                sqlcom.Parameters.Add("@Added", SqlDbType.DateTime).Value = drug.AddedWareHouseDate;

                sqlcom.ExecuteNonQuery();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        public static void SearchDrugDetails(E_DrugDetails ar)
        {
            SqlConnection conn = DAO_ConnectDatabase.Connect();
            DataTable dt;
            try
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM  Equipment.DrugDetails  WHERE DrugName = @DrugName", conn);
                sda.SelectCommand.Parameters.Add("@DrugName", SqlDbType.NVarChar).Value = ar.NameDrug;
                dt = new DataTable();
                sda.Fill(dt);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

        }

        public static DataTable RefresshDrug()
        {
            SqlConnection conn = DAO_ConnectDatabase.Connect();
            try
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from Equipment.DrugDetails ", conn);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        public static DataTable RefreshDrugType()
        {
            SqlConnection conn = DAO_ConnectDatabase.Connect();
            try
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from Equipment.DrugType ", conn);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        public static int DrugDetailsID()
        {
            SqlDataAdapter ad = new SqlDataAdapter("Select *from Equipment.DrugDetails", DAO_ConnectDatabase.Connect());
            DataTable tb = new DataTable();
            ad.Fill(tb);
            return tb.Rows.Count + 1;
        }

        public static DataTable Search(E_DrugDetails drugDetails)
        {
            SqlConnection conn = DAO_ConnectDatabase.Connect();
            DataTable dt;
            try
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM  Equipment.DrugDetails  WHERE DrugName = @DrugName", conn);
                sda.SelectCommand.Parameters.Add("@DrugName", SqlDbType.NVarChar).Value = drugDetails.NameDrug;
                dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
    }
}
